﻿CREATE FUNCTION [structure].[GetItemPath]
(
	@itemId uniqueidentifier, 
	@version int
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @Path varchar(500)

;WITH PathParts (ParentId, ItemId, ObjectPath)
AS
(
    SELECT 
		structure.Item.ParentId,
		structure.Item.Id,
		CAST(structure.ItemVersion.Name as varchar(255))
    FROM
		structure.Item
		INNER JOIN
		structure.ItemVersion
		ON
		(structure.Item.Id = structure.ItemVersion.ItemId)
    WHERE
		structure.Item.Id = @itemId
	AND
		(structure.ItemVersion.Version = @version)
    UNION ALL
    SELECT
		structure.Item.ParentId,
		structure.Item.Id,
		CAST (structure.ItemVersion.Name + '/' + PathParts.ObjectPath AS varchar(255))
    FROM
		structure.Item
		INNER JOIN
		structure.ItemVersion
		ON
		(structure.Item.Id = structure.ItemVersion.ItemId)
			INNER JOIN
			PathParts
			ON
			structure.Item.Id = PathParts.ParentId
    WHERE
		(structure.ItemVersion.Version =  structure.Item.PublishedVersion)
)
 

SELECT @Path =  'col://' + (objectpath ) FROM PathParts WHERE ParentId IS NULL
RETURN @Path
END